{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 202,
   "id": "91bed993-b5ac-4ced-af57-b6c354c7632a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import IPython\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.svm import LinearSVR\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "dc4be071-9f42-4796-a984-45888ae98987",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1_path = './Datasets/data1.xls'\n",
    "data2_path = './Datasets/data2.xls'\n",
    "data3_path = './Datasets/data3.xls'\n",
    "data4_path = './Datasets/data4.xls'\n",
    "data5_path = './Datasets/data5.xls'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a9fe727c-5191-42c5-974e-9c1edc7176e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1 = pd.read_excel(data1_path)\n",
    "data2 = pd.read_excel(data2_path)\n",
    "data3 = pd.read_excel(data3_path)\n",
    "data4 = pd.read_excel(data4_path)\n",
    "data5 = pd.read_excel(data5_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "bc0ecb20-2fde-40b6-9fad-acf7c6d1ada3",
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "id": "53eec315-b428-4e63-8b4a-f6cd8dd7f357",
   "metadata": {},
   "outputs": [],
   "source": [
    "def locate_by_str(date):\n",
    "    ds = date.split('-')\n",
    "    year = int(ds[2]) - 2007\n",
    "    month = int(ds[1])\n",
    "    day = int(ds[0])\n",
    "    sheet = datas[year]\n",
    "    day_status = sheet.iloc[day + 1, (1 + month * 8):(1 + month * 8 + 8)]\n",
    "    return day_status"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "480fb43e-bed1-41c6-8bdc-b63fb382d089",
   "metadata": {},
   "source": [
    "# 统计相关信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "803423dd-7368-4ff1-97c6-5c2d15d85711",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.concat([data1, data2, data3, data4])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5e3985ab-0e55-42d0-8689-dca9c07dd313",
   "metadata": {},
   "outputs": [],
   "source": [
    "data[data['年龄'] == \"###\"] = np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6f95ec09-2df6-4ae1-a0c7-90dc50575054",
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_data(x):\n",
    "    if type(x) == str:\n",
    "        if x.isdigit():\n",
    "            x = int(x)\n",
    "        else:\n",
    "            x = np.nan\n",
    "    else:\n",
    "        if x > 100:\n",
    "            x = np.nan\n",
    "\n",
    "    return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "id": "cefb7515-852f-492c-a499-f49b8f854764",
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_date(x):\n",
    "    if isinstance(x, datetime.datetime):\n",
    "        x = x.strftime('%d-%m-%Y')\n",
    "        if(int(x.split('-')[2]) < 2007 or int(x.split('-')[2]) > 2010):\n",
    "            x = np.nan\n",
    "    return x"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "742f56a7-7a57-45ad-a303-e6151fd499e9",
   "metadata": {},
   "source": [
    "## 填充异常值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4d83ebd3-9a2c-4e82-938a-c9f299fd81de",
   "metadata": {},
   "outputs": [],
   "source": [
    "data[\"年龄\"] = data[\"年龄\"].apply(check_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "024bc505-87fc-427f-abe5-c7090203d8d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "data[\"年龄\"] = data[\"年龄\"].fillna(np.mean(data[\"年龄\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c37c718b-8bcb-468e-a288-269238692074",
   "metadata": {},
   "outputs": [],
   "source": [
    "bins = [0,10, 20, 30, 40, 50, 60, 70, 80, 90, 100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "e3617b48-37e7-47f7-adfe-ca617dd14d6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "age_groups = pd.cut(data[\"年龄\"], bins=bins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "c203b470-9dfd-4b97-8d44-d84ebbf09d0a",
   "metadata": {},
   "outputs": [],
   "source": [
    "age_counts = age_groups.value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a20ba98d-de2a-49d0-bac8-7b75e0dc5367",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.bar(age_counts.index.astype(str), age_counts.values)\n",
    "plt.xticks(rotation=45)\n",
    "plt.xlabel(\"ages\")\n",
    "plt.ylabel('num_count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "123f4086-c8fb-4387-89a5-2fa0851ae616",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['职业'] = data['职业'].apply(check_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "9abcce17-f1ce-4f1b-9422-92eea194bed3",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['职业'] = data['职业'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "be63d066-63d6-4e35-9f67-0080abf5f28f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cnt_occu = data['职业'] .value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35c4e8b4-0c03-450b-9b58-ab36baab73c5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "plt.bar(cnt_occu.index[1:-3].astype(str), cnt_occu.iloc[1:-3],color='red')\n",
    "plt.xlabel('occupations')\n",
    "plt.ylabel('num_count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "57a47d65-be15-4d71-9472-ee9a1110733f",
   "metadata": {},
   "outputs": [],
   "source": [
    "gender_cnt = data['性别'] .value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "f50c3416-c96b-44c7-826b-1b6cd7d4c09f",
   "metadata": {},
   "outputs": [],
   "source": [
    "colors = ['gold', 'lightskyblue'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "829b4595-4017-4beb-bb98-817730e2a577",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "plt.pie(gender_cnt, labels=['1','2'],autopct='%1.1f%%')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "7e78a896-b770-4634-b3f6-0ee142e63644",
   "metadata": {},
   "outputs": [],
   "source": [
    "dateData = data5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "9d8a5460-8ecc-4d5b-83d5-d950740ce5ab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'01-01-2007'"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['发病时间'].iloc[0].strftime('%d-%m-%Y')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "id": "a99876b4-8a60-480a-8f48-6bc5f12552d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['发病时间'] = data['发病时间'].apply(check_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "f5c8da00-f792-4b30-bd94-460a77e43247",
   "metadata": {},
   "outputs": [],
   "source": [
    "date07 = pd.read_excel(data5_path,sheet_name='2007年')\n",
    "date08 = pd.read_excel(data5_path,sheet_name='2008年')\n",
    "date09 = pd.read_excel(data5_path,sheet_name='2009年')\n",
    "date10 = pd.read_excel(data5_path,sheet_name='2010年')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "id": "fce696e4-41c8-443a-9db4-c5c92b4f9233",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "attr_list = [i.replace('\\n', '') for i in date07.iloc[1].unique().tolist()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "id": "5b59a519-92a7-450b-9c43-40b15a38c6b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "datas = [date07, date08, date09, date10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "id": "bd65bc4f-9969-480c-b68e-7c67cc6d7d78",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1031.7, 1034.5, 1027, 2.1, 6, -1.1, 50, 22], dtype=object)"
      ]
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "locate_by_str('1-1-2007').values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "id": "967348ed-261c-47c6-be42-e2445c58a9ea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "发病时间\n",
       " 15-11-2010     1\n",
       " 30-07-2010     1\n",
       "01-01-1996      1\n",
       "01-01-2000      4\n",
       "01-01-2003      1\n",
       "               ..\n",
       "31-10-2010     29\n",
       "31-12-2007     31\n",
       "31-12-2008     14\n",
       "31-12-2009     31\n",
       "31-12-2010      4\n",
       "Name: count, Length: 2929, dtype: int64"
      ]
     },
     "execution_count": 206,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['发病时间'] .value_counts().sort_index()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
